﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using com.vs.General;
using com.vs.DataAccess;

namespace com.vs.DataAccess.DO
{
    public partial class ItemDO : BaseDO
    {

        #region "Get Model"
        /// <summary>
        /// 将符合数据库主键的持久对象提取到模型对象中，如数据库符合条件的持久对象不存在，则返回空对象
        /// </summary>
        /// <remarks></remarks>
        public static ItemDO GetModelByPK(Session sess, string stItem, string stCompany)
        {
            DataTable dtModel = null;
            List<KeyValuePair<string, KeyValuePair<string, object>>> dcCond = null;
            KeyValuePair<string, object> innerKvp;
            KeyValuePair<string, KeyValuePair<string, object>> outterKvp;

            try
            {
                dcCond = new List<KeyValuePair<string, KeyValuePair<string, object>>>();
                innerKvp = new KeyValuePair<string,object>("=", stItem);
                outterKvp = new KeyValuePair<string, KeyValuePair<string, object>>("ITEM", innerKvp);
                dcCond.Add(outterKvp);

                innerKvp = new KeyValuePair<string, object>("=", stCompany);
                outterKvp = new KeyValuePair<string, KeyValuePair<string, object>>("COMPANY", innerKvp);
                dcCond.Add(outterKvp);
                //dcCond.Add("ITEM", new KeyValuePair<string, object>("=", stItem));
                //dcCond.Add("COMPANY", new KeyValuePair<string, object>("=", stCompany));
                dtModel = GetTableByColumns(sess, dcCond, null);

                if (DataManager.IsEmpty(dtModel))
                    return null;
                return GetModel(dtModel.Rows[0]);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        #endregion

        #region "DataBase Operation"
        /// <summary>
        /// 在数据库里创建该数据访问对象的持久化数据
        /// </summary>
        /// <remarks></remarks>
        public string Insert(Session sess)
        {
            DataHelper helper = null;
            IDataParameter[] paras = null;
            string stSQL = "ITM_IItem";
            try
            {
                helper = new DataHelper(sess);
                paras = ValueParas(this);
                return "" + helper.GetInt(CommandType.StoredProcedure, stSQL, paras);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        public void Delete(Session sess)
        {
            DataHelper helper = null;
            string stSQL = "ITM_DItem";
            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.StoredProcedure, stSQL, UpdateWherePara(this));
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        /// <summary>
        /// 将该数据访问对象的变化同步到数据库中，如果该数据访问对象在数据库中的持久数据不存在，同步不会发生，同时也不会抛出例外
        /// </summary>
        /// <remarks></remarks>
        public void Update(Session sess)
        {
            DataHelper helper = null;
            string stSQL = "ITM_UItem";
            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.StoredProcedure, stSQL, ValueParas(this));
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        #endregion

        #region "Data Load"
        /// <summary>
        /// 装载对象ITEM的所有持久性对象到集合，如果数据量大，调用此方法会造成性能上的问题，请谨慎使用
        /// </summary>
        /// <remarks></remarks>
        public static List<ItemDO> GetAll(Session sess)
        {
            DataHelper helper = null;
            DataTable dtModel = null;
            string stSQL = "SP_ITEM_SelectAll";
            try
            {
                helper = new DataHelper(sess);
                dtModel = helper.GetTable(CommandType.StoredProcedure, stSQL);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        /// <summary>
        /// 装载指定列的值等于指定值的的所有持久性ITEM对象到集合
        /// </summary>
        /// <remarks></remarks>
        /// 

        public static DataTable GetTableByColumn(Session sess, string columnName, Object value)
        {
            DataHelper helper = null;
            IDataParameter[] paras = null;
            string stSQL = null;
            try
            {
                helper = new DataHelper(sess);
                stSQL = SelectSequel + " Where [" + columnName + "] =@Value ";
                paras = new IDataParameter[]{
                    new SqlParameter( "@Value", value)
                };
                return helper.GetTable(CommandType.Text, stSQL, paras);
            }
            catch (Exception e)
            {
                ExceptionManager.LogException(sess, e);
                return null;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        public static List<ItemDO> GetListByColumn(Session sess, string columnName, Object value)
        {
            DataTable dtModel = null;

            try
            {
                dtModel = GetTableByColumn(sess, columnName, value);
                if (DataManager.IsEmpty(dtModel))
                    return null;
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                ExceptionManager.LogException(sess, e);
                return null;
            }
        }

        /// <summary>
        /// 从DataView装载持久性ITEM对象到集合
        /// </summary>
        /// <remarks></remarks>
        private static List<ItemDO> LoadListFromDataView(DataView dv)
        {
            List<ItemDO> list = new List<ItemDO>();
            for (int index = 0; index <= dv.Count - 1; index++)
            {
                list.Add(GetModel(dv[index].Row));
            }
            return list;
        }

        /// <summary>
        /// loading data value  to property from Datarow
        /// </summary>
        /// <remarks></remarks>
        private static ItemDO GetModel(DataRow row)
        {
            ItemDO itemDO = new ItemDO();
            if (!row["INTERNAL_ITEM_NUM"].Equals(DBNull.Value)) itemDO.INTERNAL_ITEM_NUM = Convert.ToInt32(row["INTERNAL_ITEM_NUM"]);
            if (!row["ITEM"].Equals(DBNull.Value)) itemDO.ITEM = Convert.ToString(row["ITEM"]);
            if (!row["COMPANY"].Equals(DBNull.Value)) itemDO.COMPANY = Convert.ToString(row["COMPANY"]);
            if (!row["ITEM_DESC"].Equals(DBNull.Value)) itemDO.ITEM_DESC = Convert.ToString(row["ITEM_DESC"]);
            if (!row["STORAGE_TEMPLATE"].Equals(DBNull.Value)) itemDO.STORAGE_TEMPLATE = Convert.ToString(row["STORAGE_TEMPLATE"]);
            if (!row["ITEM_CLASS"].Equals(DBNull.Value)) itemDO.ITEM_CLASS = Convert.ToString(row["ITEM_CLASS"]);
            if (!row["PACKING_CLASS"].Equals(DBNull.Value)) itemDO.PACKING_CLASS = Convert.ToString(row["PACKING_CLASS"]);
            if (!row["ATTRIBUTE_TRACK"].Equals(DBNull.Value)) itemDO.ATTRIBUTE_TRACK = Convert.ToString(row["ATTRIBUTE_TRACK"]);
            if (!row["LOCATING_RULE"].Equals(DBNull.Value)) itemDO.LOCATING_RULE = Convert.ToString(row["LOCATING_RULE"]);
            if (!row["ALLOCATION_RULE"].Equals(DBNull.Value)) itemDO.ALLOCATION_RULE = Convert.ToString(row["ALLOCATION_RULE"]);
            if (!row["DATE_TIME_STAMP"].Equals(DBNull.Value)) itemDO.DATE_TIME_STAMP = Convert.ToDateTime(row["DATE_TIME_STAMP"]);
            if (!row["USER_STAMP"].Equals(DBNull.Value)) itemDO.USER_STAMP = Convert.ToString(row["USER_STAMP"]);
            if (!row["ITEM_CATEGORY1"].Equals(DBNull.Value)) itemDO.ITEM_CATEGORY1 = Convert.ToString(row["ITEM_CATEGORY1"]);
            if (!row["ITEM_CATEGORY2"].Equals(DBNull.Value)) itemDO.ITEM_CATEGORY2 = Convert.ToString(row["ITEM_CATEGORY2"]);
            if (!row["ITEM_CATEGORY3"].Equals(DBNull.Value)) itemDO.ITEM_CATEGORY3 = Convert.ToString(row["ITEM_CATEGORY3"]);
            if (!row["ITEM_CATEGORY4"].Equals(DBNull.Value)) itemDO.ITEM_CATEGORY4 = Convert.ToString(row["ITEM_CATEGORY4"]);
            if (!row["ITEM_CATEGORY5"].Equals(DBNull.Value)) itemDO.ITEM_CATEGORY5 = Convert.ToString(row["ITEM_CATEGORY5"]);
            if (!row["ITEM_CATEGORY6"].Equals(DBNull.Value)) itemDO.ITEM_CATEGORY6 = Convert.ToString(row["ITEM_CATEGORY6"]);
            if (!row["ITEM_CATEGORY7"].Equals(DBNull.Value)) itemDO.ITEM_CATEGORY7 = Convert.ToString(row["ITEM_CATEGORY7"]);
            if (!row["ITEM_CATEGORY8"].Equals(DBNull.Value)) itemDO.ITEM_CATEGORY8 = Convert.ToString(row["ITEM_CATEGORY8"]);
            if (!row["ACTIVE"].Equals(DBNull.Value)) itemDO.ACTIVE = Convert.ToString(row["ACTIVE"]);
            if (!row["DAYS_TO_EXPIRE"].Equals(DBNull.Value)) itemDO.DAYS_TO_EXPIRE = Convert.ToInt32(row["DAYS_TO_EXPIRE"]);
            if (!row["REPLENISHMENT_RULE"].Equals(DBNull.Value)) itemDO.REPLENISHMENT_RULE = Convert.ToString(row["REPLENISHMENT_RULE"]);
            if (!row["EMPTY_LOC_RULE"].Equals(DBNull.Value)) itemDO.EMPTY_LOC_RULE = Convert.ToString(row["EMPTY_LOC_RULE"]);
            if (!row["ATTRIBUTE_TEMPLATE_NUM"].Equals(DBNull.Value)) itemDO.ATTRIBUTE_TEMPLATE_NUM = Convert.ToInt32(row["ATTRIBUTE_TEMPLATE_NUM"]);

            

            return itemDO;
        }

        #endregion

        #region "Other function"
        /// <summary>
        /// 该数据访问对象从数据库中提取数据的Sql语句 
        /// </summary>
        /// <remarks></remarks>
        private static string SelectSequel
        {
            get
            {
                return "Select	*  From [ITEM] WITH(NOLOCK) ";
            }
        }

        /// <summary>
        /// 该数据访问对象的属性值装载到数据库更新参数数组
        /// </summary>
        /// <remarks></remarks>
        private static IDataParameter[] ValueParas(ItemDO itemDO)
        {
            SqlParameter[] paras = new SqlParameter[25];
            paras[0] = new SqlParameter("@ITEM", itemDO.ITEM);
            paras[1] = new SqlParameter("@COMPANY", itemDO.COMPANY);
            paras[2] = new SqlParameter("@ITEM_DESC", itemDO.ITEM_DESC == String.Empty ? DBNull.Value : (object)itemDO.ITEM_DESC);
            paras[3] = new SqlParameter("@STORAGE_TEMPLATE", itemDO.STORAGE_TEMPLATE == String.Empty ? DBNull.Value : (object)itemDO.STORAGE_TEMPLATE);
            paras[4] = new SqlParameter("@ITEM_CLASS", itemDO.ITEM_CLASS == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CLASS);
            paras[5] = new SqlParameter("@PACKING_CLASS", itemDO.PACKING_CLASS == String.Empty ? DBNull.Value : (object)itemDO.PACKING_CLASS);
            paras[6] = new SqlParameter("@ATTRIBUTE_TRACK", itemDO.ATTRIBUTE_TRACK);
            paras[7] = new SqlParameter("@LOCATING_RULE", itemDO.LOCATING_RULE == String.Empty ? DBNull.Value : (object)itemDO.LOCATING_RULE);
            paras[8] = new SqlParameter("@ALLOCATION_RULE", itemDO.ALLOCATION_RULE == String.Empty ? DBNull.Value : (object)itemDO.ALLOCATION_RULE);
            paras[9] = new SqlParameter("@USER_STAMP", itemDO.USER_STAMP == String.Empty ? DBNull.Value : (object)itemDO.USER_STAMP);
            paras[10] = new SqlParameter("@DATE_TIME_STAMP", itemDO.DATE_TIME_STAMP == DateTime.MinValue ? DBNull.Value : (object)itemDO.DATE_TIME_STAMP);
            paras[11] = new SqlParameter("@ITEM_CATEGORY1", itemDO.ITEM_CATEGORY1 == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CATEGORY1);
            paras[12] = new SqlParameter("@ITEM_CATEGORY2", itemDO.ITEM_CATEGORY2 == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CATEGORY2);
            paras[13] = new SqlParameter("@ITEM_CATEGORY3", itemDO.ITEM_CATEGORY3 == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CATEGORY3);
            paras[14] = new SqlParameter("@ITEM_CATEGORY4", itemDO.ITEM_CATEGORY4 == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CATEGORY4);
            paras[15] = new SqlParameter("@ITEM_CATEGORY5", itemDO.ITEM_CATEGORY5 == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CATEGORY5);
            paras[16] = new SqlParameter("@ITEM_CATEGORY6", itemDO.ITEM_CATEGORY6 == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CATEGORY6);
            paras[17] = new SqlParameter("@ITEM_CATEGORY7", itemDO.ITEM_CATEGORY7 == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CATEGORY7);
            paras[18] = new SqlParameter("@ITEM_CATEGORY8", itemDO.ITEM_CATEGORY8 == String.Empty ? DBNull.Value : (object)itemDO.ITEM_CATEGORY8);
            paras[19] = new SqlParameter("@ACTIVE", itemDO.ACTIVE == String.Empty ? DBNull.Value : (object)itemDO.ACTIVE);
            paras[20] = new SqlParameter("@DAYS_TO_EXPIRE", itemDO.DAYS_TO_EXPIRE);
            paras[21] = new SqlParameter("@INTERNAL_ITEM_NUM", itemDO.INTERNAL_ITEM_NUM);
            paras[22] = new SqlParameter("@REPLENISHMENT_RULE", itemDO.REPLENISHMENT_RULE == String.Empty ? DBNull.Value :(object) itemDO.REPLENISHMENT_RULE);
            paras[23] = new SqlParameter("@EMPTY_LOC_RULE", itemDO.EMPTY_LOC_RULE == String.Empty ? DBNull.Value : (object)itemDO.EMPTY_LOC_RULE);
            paras[24] = new SqlParameter("@ATTRIBUTE_TEMPLATE_NUM", itemDO.ATTRIBUTE_TEMPLATE_NUM);
            

            paras[0].DbType = DbType.String;
            paras[1].DbType = DbType.String;
            paras[2].DbType = DbType.String;
            paras[3].DbType = DbType.String;
            paras[4].DbType = DbType.String;
            paras[5].DbType = DbType.String;
            paras[6].DbType = DbType.String;
            paras[7].DbType = DbType.String;
            paras[8].DbType = DbType.String;
            paras[9].DbType = DbType.String;
            paras[10].DbType = DbType.DateTime;
            paras[11].DbType = DbType.String;
            paras[12].DbType = DbType.String;
            paras[13].DbType = DbType.String;
            paras[14].DbType = DbType.String;
            paras[15].DbType = DbType.String;
            paras[16].DbType = DbType.String;
            paras[17].DbType = DbType.String;
            paras[18].DbType = DbType.String;
            paras[19].DbType = DbType.String;
            paras[20].DbType = DbType.Decimal;
            paras[21].DbType = DbType.Decimal;
            paras[22].DbType = DbType.String;
            paras[23].DbType = DbType.String;
            paras[24].DbType = DbType.Decimal;
            return paras;
        }

        private static IDataParameter[] UpdateWherePara(ItemDO itemDO)
        {
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@ITEM", itemDO.ITEM);
            paras[1] = new SqlParameter("@COMPANY", itemDO.COMPANY);
            return paras;
        }

        #endregion


        public static List<ItemDO> GetListByColumns(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy)
        {
            DataTable dtModel = null;

            try
            {
                dtModel = GetTableByColumns(sess, dcParam, stOrderBy);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static DataTable GetTableByColumns(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy)
        {
            DataHelper helper = null;
            StringBuilder sbSQL = null;
            int i = 0;
            IDataParameter[] pars = null;

            try
            {
                helper = new DataHelper(sess);
                sbSQL = new StringBuilder(SelectSequel)
                    .Append(" WHERE 1=1 ");

                pars = new IDataParameter[dcParam.Count];
                foreach (KeyValuePair<string, KeyValuePair<string, object>> de in dcParam)
                {
                    sbSQL.Append(" AND ").Append(de.Key).Append(de.Value.Key).Append("@Value").Append(i);
                    pars[i] = new SqlParameter("@Value" + i, de.Value.Value);
                    i++;
                }

                if (!StringManager.IsEmpty(stOrderBy))
                {
                    sbSQL.Append(stOrderBy);
                }
                return helper.GetTable(CommandType.Text, sbSQL.ToString(), pars);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }
    }
}
